﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace DALL.SysManage
{
    public class T_PUB_MATERIAL
    {
        public T_PUB_MATERIAL()
		{}

        /// <summary>
        /// 智能提示元素名称
        /// </summary>
        /// <returns></returns>
        public DataSet GetMaterialName()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select DISTINCT MATERIAL_NAME FROM T_PUB_MATERIAL ");
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

		#region  Method
       
        /// <summary>
        /// 获得元素信息和所属元素分类信息
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetMaterialInfo(string strWhere)
        {
            StringBuilder varname1 = new StringBuilder();
            varname1.Append("select MATERIAL_ID,  \n");
            varname1.Append("       MATERIAL_NAME, \n");
            varname1.Append("       a.TYPE_ID, \n");
            varname1.Append("       b.TYPE_NAME, \n");
            varname1.Append("       a.PRICE, \n");
            varname1.Append("       a.REMARK, \n");
            varname1.Append("       a.IMAGEURL \n");
            varname1.Append("from T_PUB_MATERIAL AS a, \n");
            varname1.Append("       T_PUB_MATERIALTYPE as b \n");
            varname1.Append("where b.TYPE_ID = a.TYPE_ID \n");
            varname1.Append("and b.ISUSED = '1'\n");
            if (strWhere.Trim() != "")
            {
                varname1.Append(strWhere);
            }
            Database db = DatabaseFactory.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, varname1.ToString());

        }


        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int MATERIAL_ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_PUB_MATERIAL where MATERIAL_ID=@MATERIAL_ID ");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "MATERIAL_ID", DbType.Int32, MATERIAL_ID);
            int cmdresult;
            object obj = db.ExecuteScalar(dbCommand);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(Model.SysManage.T_PUB_MATERIAL model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into T_PUB_MATERIAL(");
            strSql.Append("TYPE_ID,MATERIAL_NAME,PRICE,REMARK,FLAG,PROPERTY,UNIT,COLOR,SPECIFICATIONS,ISUSED,IMAGEURL)");

			strSql.Append(" values (");
            strSql.Append("@TYPE_ID,@MATERIAL_NAME,@PRICE,@REMARK,@FLAG,@PROPERTY,@UNIT,@COLOR,@SPECIFICATIONS,@ISUSED,@IMAGEURL)");
			strSql.Append(";select @@IDENTITY");
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
			db.AddInParameter(dbCommand, "TYPE_ID", DbType.Int32, model.TYPE_ID);
			db.AddInParameter(dbCommand, "MATERIAL_NAME", DbType.String, model.MATERIAL_NAME);
			db.AddInParameter(dbCommand, "PRICE", DbType.String, model.PRICE);
			db.AddInParameter(dbCommand, "REMARK", DbType.String, model.REMARK);
			db.AddInParameter(dbCommand, "FLAG", DbType.Int32, model.FLAG);
			db.AddInParameter(dbCommand, "PROPERTY", DbType.Int32, model.PROPERTY);
			db.AddInParameter(dbCommand, "UNIT", DbType.Int32, model.UNIT);
			db.AddInParameter(dbCommand, "COLOR", DbType.Int32, model.COLOR);
			db.AddInParameter(dbCommand, "SPECIFICATIONS", DbType.String, model.SPECIFICATIONS);
            db.AddInParameter(dbCommand, "ISUSED", DbType.Int32, model.ISUSED);
            db.AddInParameter(dbCommand, "IMAGEURL", DbType.String, model.IMAGEURL);
			int result;
			object obj = db.ExecuteScalar(dbCommand);
			if(!int.TryParse(obj.ToString(),out result))
			{
				return 0;
			}
			return result;
		}
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.SysManage.T_PUB_MATERIAL model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_PUB_MATERIAL set ");
            strSql.Append("TYPE_ID=@TYPE_ID,");
            strSql.Append("MATERIAL_NAME=@MATERIAL_NAME,");
            strSql.Append("PRICE=@PRICE,");
            strSql.Append("REMARK=@REMARK,");
            strSql.Append("FLAG=@FLAG,");
            strSql.Append("PROPERTY=@PROPERTY,");
            strSql.Append("UNIT=@UNIT,");
            strSql.Append("COLOR=@COLOR,");
            strSql.Append("SPECIFICATIONS=@SPECIFICATIONS,");
            strSql.Append("IMAGEURL=@IMAGEURL");
            strSql.Append(" where MATERIAL_ID=@MATERIAL_ID ");
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "MATERIAL_ID", DbType.Int32, model.MATERIAL_ID);
            db.AddInParameter(dbCommand, "TYPE_ID", DbType.Int32, model.TYPE_ID);
            db.AddInParameter(dbCommand, "MATERIAL_NAME", DbType.String, model.MATERIAL_NAME);
            db.AddInParameter(dbCommand, "PRICE", DbType.String, model.PRICE);
            db.AddInParameter(dbCommand, "REMARK", DbType.String, model.REMARK);
            db.AddInParameter(dbCommand, "FLAG", DbType.Int32, model.FLAG);
            db.AddInParameter(dbCommand, "PROPERTY", DbType.Int32, model.PROPERTY);
            db.AddInParameter(dbCommand, "UNIT", DbType.Int32, model.UNIT);
            db.AddInParameter(dbCommand, "COLOR", DbType.Int32, model.COLOR);
            db.AddInParameter(dbCommand, "SPECIFICATIONS", DbType.String, model.SPECIFICATIONS);
            db.AddInParameter(dbCommand, "IMAGEURL", DbType.String, model.IMAGEURL);
            int rows = db.ExecuteNonQuery(dbCommand);

            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int MATERIAL_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_PUB_MATERIAL set ");
            strSql.Append("ISUSED='0'");
            strSql.Append(" where MATERIAL_ID=@MATERIAL_ID ");
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "MATERIAL_ID", DbType.Int32, MATERIAL_ID);
            int rows = db.ExecuteNonQuery(dbCommand);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Model.SysManage.T_PUB_MATERIAL GetModel(int MATERIAL_ID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select MATERIAL_ID,TYPE_ID,MATERIAL_NAME,PRICE,REMARK,FLAG,PROPERTY,UNIT,COLOR,SPECIFICATIONS,IMAGEURL from T_PUB_MATERIAL ");
			strSql.Append(" where MATERIAL_ID=@MATERIAL_ID ");
			Database db = DatabaseFactory.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
			db.AddInParameter(dbCommand, "MATERIAL_ID", DbType.Int32,MATERIAL_ID);
			Model.SysManage.T_PUB_MATERIAL model=null;
			using (IDataReader dataReader = db.ExecuteReader(dbCommand))
			{
				if(dataReader.Read())
				{
					model=ReaderBind(dataReader);
				}
			}
			return model;
		}
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select MATERIAL_ID,TYPE_ID,MATERIAL_NAME,PRICE,REMARK,FLAG,PROPERTY,UNIT,COLOR,SPECIFICATIONS,IMAGEURL");
            strSql.Append(" FROM T_PUB_MATERIAL ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            Database db = DatabaseFactory.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

		/// <summary>
		/// 获得数据列表（比DataSet效率高，推荐使用）
		/// </summary>
		public List<Model.SysManage.T_PUB_MATERIAL> GetListArray(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select MATERIAL_ID,TYPE_ID,MATERIAL_NAME,PRICE,REMARK,FLAG,PROPERTY,UNIT,COLOR,SPECIFICATIONS,IMAGEURL ");
			strSql.Append(" FROM T_PUB_MATERIAL ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			List<Model.SysManage.T_PUB_MATERIAL> list = new List<Model.SysManage.T_PUB_MATERIAL>();
			Database db = DatabaseFactory.CreateDatabase();
			using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
			{
				while (dataReader.Read())
				{
					list.Add(ReaderBind(dataReader));
				}
			}
			return list;
		}
/// <summary>
		/// 对象实体绑定数据
		/// </summary>
		public Model.SysManage.T_PUB_MATERIAL ReaderBind(IDataReader dataReader)
		{
			Model.SysManage.T_PUB_MATERIAL model=new Model.SysManage.T_PUB_MATERIAL();
			object ojb; 
			ojb = dataReader["MATERIAL_ID"];
			if(ojb != null && ojb != DBNull.Value)
			{
				model.MATERIAL_ID=(int)ojb;
			}
			ojb = dataReader["TYPE_ID"];
			if(ojb != null && ojb != DBNull.Value)
			{
				model.TYPE_ID=(int)ojb;
			}
			model.MATERIAL_NAME=dataReader["MATERIAL_NAME"].ToString();
			model.PRICE=dataReader["PRICE"].ToString();
			model.REMARK=dataReader["REMARK"].ToString();
			ojb = dataReader["FLAG"];
			if(ojb != null && ojb != DBNull.Value)
			{
				model.FLAG=(int)ojb;
			}
			ojb = dataReader["PROPERTY"];
			if(ojb != null && ojb != DBNull.Value)
			{
				model.PROPERTY=(int)ojb;
			}
			ojb = dataReader["UNIT"];
			if(ojb != null && ojb != DBNull.Value)
			{
				model.UNIT=(int)ojb;
			}
			ojb = dataReader["COLOR"];
			if(ojb != null && ojb != DBNull.Value)
			{
				model.COLOR=(int)ojb;
			}
			model.SPECIFICATIONS=dataReader["SPECIFICATIONS"].ToString();
            model.IMAGEURL = dataReader["IMAGEURL"].ToString();
			return model;
		}


		#endregion  Method
    }
}
